package com.ratingbay.console.dao;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import javax.xml.crypto.Data;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.ratingbay.console.model.Game;
import com.ratingbay.console.model.Link;
import com.ratingbay.console.model.SentimentScoreHistory;

public interface GameDAO extends JpaRepository<Game, Long> {

    public Game findById(Long idGame);
    
    @Query("select g from Game g where g.id = ?1 and g.status = 1")
    public List<Game> findEnableGame(Long gameId);

	@Query("select g from Game g where g.status = 1 and g.id = ?1")
	public List<Game> findGameById(Long idGames);
	//user page games by user friend
	
	@Query("select g from Game g where g.status=1 and g.id in ?1")
	public List<Game> findGameIdIn(List<Long> idGames);
	//user page games by user

	@Query("select g from Game g where g.status=1 and g.isHeadline = true and g.releaseDate between ?1 and ?2")
	public Page<Game> findHeadlineByReleaseDate(Timestamp startdate, Timestamp finishdate, Pageable pageable);
	//home page headline banner used

	@Query( 
			value = "select DISTINCT b.id_game from (select  t.id_game from rb_tweet t where t.id_game <> ?1 and t.user_id in "
					+ "(select DISTINCT a.user_id from (select  t.user_id from rb_tweet t where t.id_game=?1  limit 1000) a limit 1000) "
					+ "limit 1000 offset 0) b limit 5",
			nativeQuery=true
			)
		public List<Long> findRelatedGameIdsByGame(Long idGame, int pageNum, int pageSize);

	@Query(
		value="select g.* from game_summary t,game g where g.status = 1 and t.id_game = g.id and  g.release_date between ?1 and ?2 order by t.tweet_count desc,g.id limit ?4 offset ?3*?4",
		nativeQuery=true
		)
	public List<Game> findTopTweetedGame(Timestamp startdate, Timestamp finishdate, int pageNum, int pageSize);
	//home page most tweeted games

	@Query(
			value="select g.* from game_summary t,game g where g.status = 1 and t.id_game = g.id and g.id!=138 and g.id!=139 order by t.tweet_count desc,g.id limit ?2 offset ?1*?2",
			nativeQuery=true
			)
		public List<Game> findTopTweetedGameRestul(int pageNum, int pageSize);
	
	@Query(
		value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null and gs.sentiment_score !=0 and g.id = gs.id_game order by gs.sentiment_score desc,g.id limit ?4 offset ?3*?4",
		nativeQuery=true
		)
	public List<Game> findTopRatedGame(Timestamp startdate, Timestamp finishdate, int pageNum, int pageSize);
	//home page games order by score desc

	@Query(
		value="select count(g.id) from game g where g.status=1 and LOWER(g.title) like LOWER(?1)",
		nativeQuery=true
		)
	public Long findCountGameByTitleLike(String title);
	//search page show games count 

	@Query(
		value="select g.* from game g where g.status=1 and LOWER(g.title) like LOWER(?1) limit ?3 offset ?3*?2",
		nativeQuery=true
	)
	public List<Game> findGameByTitleLike(String title, int pageNum, int pageSize);
	//search page show games

	@Query(
		value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and LOWER(g.title) like LOWER(?1) order by gs.tweet_count DESC,g.id limit ?3 offset ?3*?2",
		nativeQuery=true
	)
	public List<Game> findGameByTitleLikeDESCOrderTC(String title, int pageNum, int pageSize);
	@Query(
		value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and LOWER(g.title) like LOWER(?1) order by gs.tweet_count ASC,g.id limit ?3 offset ?3*?2",
		nativeQuery=true
	)
	public List<Game> findGameByTitleLikeASCOrderTC(String title, int pageNum, int pageSize);

	@Query(
		value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null and g.id = gs.id_game and LOWER(g.title) like LOWER(?1) order by gs.sentiment_score DESC,g.id limit ?3 offset ?3*?2",
		nativeQuery=true
	)
	public List<Game> findGameByTitleLikeDESCOrderSS(String title, int pageNum, int pageSize);
	@Query(
		value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null and g.id = gs.id_game and LOWER(g.title) like LOWER(?1) order by gs.sentiment_score ASC,g.id limit ?3 offset ?3*?2",
		nativeQuery=true
	)
	public List<Game> findGameByTitleLikeASCOrderSS(String title, int pageNum, int pageSize);

	@Query(
		value="select g.* from game g where g.status = 1 and LOWER(g.title) like LOWER(?1) order by g.release_date DESC,g.id limit ?3 offset ?3*?2",
		nativeQuery=true
	)
	public List<Game> findGameByTitleLikeDESCOrderRD(String title, int pageNum, int pageSize);
	@Query(
		value="select g.* from game g where g.status = 1 and LOWER(g.title) like LOWER(?1) order by g.release_date ASC,g.id limit ?3 offset ?3*?2",
		nativeQuery=true
	)
	public List<Game> findGameByTitleLikeASCOrderRD(String title, int pageNum, int pageSize); 
		
	//game genre
	@Query(
			value="select count(distinct id_game) from game_genre where id_genre = ?1",
			nativeQuery=true
		)
	public Long findCountGameByGenre(Long genre); 
	
	@Query(
			value="select * from game where status = 1 and id in(select id_game from game_genre where id_genre = ?1) order by score desc,id limit ?3 offset ?3*?2",
			nativeQuery=true
		)
	public List<Game> findGameByGenre(Long genre,int pageNum,int pageSize); 
	
		@Query(
			value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and g.id in(select id_game from game_genre where id_genre = ?1) order by gs.tweet_count DESC,g.id limit ?3 offset ?3*?2",
			nativeQuery=true
		)
		public List<Game> findGameByGenreLikeDESCOrderTC(Long genre, int pageNum, int pageSize);
		@Query(
			value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and g.id in(select id_game from game_genre where id_genre = ?1) order by gs.tweet_count ASC,g.id limit ?3 offset ?3*?2",
			nativeQuery=true
		)
		public List<Game> findGameByGenreLikeASCOrderTC(Long genre, int pageNum, int pageSize);

		@Query(
			value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null and g.id = gs.id_game and g.id in(select id_game from game_genre where id_genre = ?1) order by gs.sentiment_score DESC,g.id limit ?3 offset ?3*?2",
			nativeQuery=true
		)
		public List<Game> findGameByGenreLikeDESCOrderSS(Long genre, int pageNum, int pageSize);
		@Query(
			value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null and g.id = gs.id_game and g.id in(select id_game from game_genre where id_genre = ?1) order by gs.sentiment_score ASC,g.id limit ?3 offset ?3*?2",
			nativeQuery=true
		)
		public List<Game> findGameByGenreLikeASCOrderSS(Long genre, int pageNum, int pageSize);

		@Query(
			value="select g.* from game g where g.status = 1 and g.id in(select id_game from game_genre where id_genre = ?1) order by g.release_date DESC,g.id limit ?3 offset ?3*?2",
			nativeQuery=true
		)
		public List<Game> findGameByGenreLikeDESCOrderRD(Long genre, int pageNum, int pageSize);
		@Query(
			value="select g.* from game g where g.status = 1 and g.id in(select id_game from game_genre where id_genre = ?1) order by g.release_date ASC,g.id limit ?3 offset ?3*?2",
			nativeQuery=true
		)
		public List<Game> findGameByGenreLikeASCOrderRD(Long genre, int pageNum, int pageSize); 
		
	//game platform
		@Query(
				value="select count(distinct id_game) from game_platform where id_platform in(select id from platform where LOWER(name) like LOWER(?1))",
				nativeQuery=true
			)
		public Long findCountGameByPlatform(String platform); 
		
		@Query(
				value="select * from game where status = 1 and id in(select id_game from game_platform where id_platform in(select id from platform where LOWER(name) like LOWER(?1))) order by release_date desc,id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
		public List<Game> findGameByPlatform(String platform,int pageNum,int pageSize); 
		
		@Query(
				value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and g.id "
						+ "in(select id_game from game_platform where id_platform in(select id from platform where LOWER(name) like LOWER(?1)))"
						+ " order by gs.tweet_count DESC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByPlatformLikeDESCOrderTC(String platform, int pageNum, int pageSize);
			@Query(
				value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and g.id"
						+ " in(select id_game from game_platform where id_platform in(select id from platform where LOWER(name) like LOWER(?1)))"
						+ " order by gs.tweet_count ASC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByPlatformLikeASCOrderTC(String platform, int pageNum, int pageSize);

			@Query(
				value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null and g.id = gs.id_game and g.id"
						+ " in(select id_game from game_platform where id_platform in(select id from platform where LOWER(name) like LOWER(?1)))"
						+ " order by gs.sentiment_score DESC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByPlatformLikeDESCOrderSS(String platform, int pageNum, int pageSize);
			@Query(
				value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null and g.id = gs.id_game and g.id"
						+ " in(select id_game from game_platform where id_platform in(select id from platform where LOWER(name) like LOWER(?1)))"
						+ " order by gs.sentiment_score ASC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByPlatformLikeASCOrderSS(String platform, int pageNum, int pageSize);

			@Query(
				value="select g.* from game g where g.status = 1 and g.id in(select id_game from game_platform where id_platform in(select id from platform where LOWER(name) like LOWER(?1))) "
						+ "order by g.release_date DESC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByPlatformLikeDESCOrderRD(String platform, int pageNum, int pageSize);
			@Query(
				value="select g.* from game g where g.status = 1 and g.id in(select id_game from game_platform where id_platform in(select id from platform where LOWER(name) like LOWER(?1))) "
						+ "order by g.release_date ASC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByPlatformLikeASCOrderRD(String platform, int pageNum, int pageSize); 
		
	//game genre and platform
	@Query(
			value="select name from genre where id in(select id_genre from game_genre where id_game = ?1)",
			nativeQuery=true
		)
	public List<String> findGenreById(Long id);
	@Query(
			value="select name from platform where id in(select id_platform from game_platform where id_game = ?1)",
			nativeQuery=true
		)
	public List<String> findPlatformById(Long id);
	
	//released game
		@Query(
				value="select count(distinct id) from game where status = 1 and release_date < ?1",
				nativeQuery=true
			)
		public Long findCountGameByTimeBefore(Date now); 
		
			@Query(
				value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and g.release_date < ?1 "
						+ "order by gs.tweet_count DESC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByTimeBeforeLikeDESCOrderTC(Date now, int pageNum, int pageSize);
			@Query(
				value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and "
						+ "g.release_date < ?1 order by "
						+ "gs.tweet_count ASC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByTimeBeforeLikeASCOrderTC(Date now, int pageNum, int pageSize);

			@Query(
				value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null "
						+ "and g.id = gs.id_game and g.release_date < ?1"
						+ "order by gs.sentiment_score DESC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByTimeBeforeLikeDESCOrderSS(Date now, int pageNum, int pageSize);
			@Query(
				value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null "
						+ "and g.id = gs.id_game and g.release_date < ?1 "
						+ "order by gs.sentiment_score ASC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByTimeBeforeLikeASCOrderSS(Date now, int pageNum, int pageSize);

			@Query(
				value="select g.* from game g where g.status = 1 and g.release_date < ?1 "
						+ "order by g.release_date DESC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByTimeBeforeLikeDESCOrderRD(Date now, int pageNum, int pageSize);
			@Query(
				value="select g.* from game g where g.status = 1 and g.release_date < ?1 "
						+ "order by g.release_date ASC,g.id limit ?3 offset ?3*?2",
				nativeQuery=true
			)
			public List<Game> findGameByTimeBeforeLikeASCOrderRD(Date now, int pageNum, int pageSize); 
			
			//coming game
			@Query(
					value="select count(distinct id) from game where status = 1 and release_date > ?1",
					nativeQuery=true
				)
			public Long findCountGameByTimeAfter(Date now); 
			
				@Query(
					value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and g.release_date > ?1 "
							+ "order by gs.tweet_count DESC,g.id limit ?3 offset ?3*?2",
					nativeQuery=true
				)
				public List<Game> findGameByTimeAfterLikeDESCOrderTC(Date now, int pageNum, int pageSize);
				@Query(
					value="select g.* from game g, game_summary gs where g.status = 1 and g.id = gs.id_game and "
							+ "g.release_date > ?1 order by "
							+ "gs.tweet_count ASC,g.id limit ?3 offset ?3*?2",
					nativeQuery=true
				)
				public List<Game> findGameByTimeAfterLikeASCOrderTC(Date now, int pageNum, int pageSize);

				@Query(
					value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null "
							+ "and g.id = gs.id_game and g.release_date > ?1"
							+ "order by gs.sentiment_score DESC,g.id limit ?3 offset ?3*?2",
					nativeQuery=true
				)
				public List<Game> findGameByTimeAfterLikeDESCOrderSS(Date now, int pageNum, int pageSize);
				@Query(
					value="select g.* from game g, game_summary gs where g.status = 1 and gs.sentiment_score is not null "
							+ "and g.id = gs.id_game and g.release_date > ?1 "
							+ "order by gs.sentiment_score ASC,g.id limit ?3 offset ?3*?2",
					nativeQuery=true
				)
				public List<Game> findGameByTimeAfterLikeASCOrderSS(Date now, int pageNum, int pageSize);

				@Query(
					value="select g.* from game g where g.status = 1 and g.release_date > ?1 "
							+ "order by g.release_date DESC,g.id limit ?3 offset ?3*?2",
					nativeQuery=true
				)
				public List<Game> findGameByTimeAfterLikeDESCOrderRD(Date now, int pageNum, int pageSize);
				@Query(
					value="select g.* from game g where g.status = 1 and g.release_date > ?1 "
							+ "order by g.release_date ASC,g.id limit ?3 offset ?3*?2",
					nativeQuery=true
				)
				public List<Game> findGameByTimeAfterLikeASCOrderRD(Date now, int pageNum, int pageSize); 
				
}
